package pay.common;

import java.io.Serializable;
import java.math.BigDecimal;
import java.text.ParseException;
import java.util.HashMap;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateUtils;

public class SqlFilter  implements Serializable{

	/**
	 * 
	 */
	private static final long serialVersionUID = 5928024712462650040L;
	
	private Map<String, String> req_params = new HashMap<String, String>();// 条件参数
	// private HttpServletRequest request;// 为了获取request里面传过来的动态参数
	private Map<String, Object> params = new HashMap<String, Object>();// 条件参数
	private StringBuffer hql = new StringBuffer();
	private String sort;// 排序字段
	private String order = "asc";// asc/desc
	private String tableName;

	/**
	 * 默认构造
	 */
	public SqlFilter(String tableName) {
		this.tableName = tableName;
	}

	/**
	 * 带参构造
	 * 
	 * @param request
	 */
	public SqlFilter(Map<String, String> map) {
		addFilter(map);
	}

	/**
	 * 添加排序字段
	 * 
	 * @param sort
	 */
	public void addSort(String sort) {
		this.sort = sort;
	}

	/**
	 * 添加排序方法，默认asc升序
	 * 
	 * @param order
	 */
	public void addOrder(String order) {
		this.order = order;
	}

	/**
	 * 转换SQL操作符
	 * 
	 * @param operator
	 * @return
	 */
	private String getSqlOperator(String operator) {
		if (StringUtils.equalsIgnoreCase(operator, "EQ")) {
			return " = ";
		}
		if (StringUtils.equalsIgnoreCase(operator, "NE")) {
			return " != ";
		}
		if (StringUtils.equalsIgnoreCase(operator, "LT")) {
			return " < ";
		}
		if (StringUtils.equalsIgnoreCase(operator, "GT")) {
			return " > ";
		}
		if (StringUtils.equalsIgnoreCase(operator, "LE")) {
			return " <= ";
		}
		if (StringUtils.equalsIgnoreCase(operator, "GE")) {
			return " >= ";
		}
		if (StringUtils.equalsIgnoreCase(operator, "LK")
				|| StringUtils.equalsIgnoreCase(operator, "RLK")
				|| StringUtils.equalsIgnoreCase(operator, "LLK")) {
			return " like ";
		}
		return "";
	}

	/**
	 * 获得添加过滤字段后的HQL
	 * 
	 * @return
	 */
	public String getWhereHql() {
		return hql.toString();
	}

	/**
	 * 获得添加过滤字段后加上排序字段的HQL
	 * 
	 * @return
	 */
	public String getWhereAndOrderHql() {
		if (!StringUtils.isBlank(sort) && !StringUtils.isBlank(order)) {
			if (sort.indexOf(".") < 1) {
				sort = "t." + sort;
			}
			hql.append(" order by " + sort + " " + order + " ");// 添加排序信息
		} else {
			if (req_params != null) {
				String s = req_params.get("sort");
				String o = req_params.get("order");
				if (!StringUtils.isBlank(s)) {
					sort = s;
				}
				if (!StringUtils.isBlank(o)) {
					order = o;
				}
				if (!StringUtils.isBlank(sort) && !StringUtils.isBlank(order)) {
					if (sort.indexOf(".") < 1) {
						sort = "t." + sort;
					}
					hql.append(" order by " + sort + " " + order + " ");// 添加排序信息
				}
			}
		}
		return hql.toString();
	}

	/**
	 * 获得过滤字段参数和值
	 * 
	 * @return
	 */
	public Map<String, Object> getParams() {
		return params;
	}

	/**
	 * 添加过滤
	 * 
	 * @param request
	 */
	public void addFilter(Map<String, String> map) {
		for (String key : map.keySet()) {
			addFilter(key, map.get(key));
		}
	}

	/**
	 * 增加查询条件
	 * 
	 * @param name 实体字段名称
	 * @param type 实体类型 （S:String L:Long I:Integer D:Date ST:Short BD:BigDecimal FT:Float)
	 * @param eq   操作符（// EQ 相等 // NE 不等 // LT 小于 // GT 大于 // LE 小于等于 // GE 大于等于 // LK 模糊 // RLK 右模糊// LLK 左模糊）
	 * @param value 条件值
	 */
	public void addFilter(String name, String type, String eq, String value) {
		Map<String, String> map = new HashMap<String, String>();
		String key = "QUERY_" + "_t#" + name + "_" + type + "_" + eq;
		map.put(key, value);
		this.addFilter(map);
	}

	/**
	 * 添加过滤
	 * 
	 * 举例，name传递：QUERY_t#id_S_EQ
	 * 
	 * 举例，value传递：0
	 * 
	 * @param params
	 */
	public void addFilter(String name, String value) {
		if (name != null && value != null) {
			if (name.startsWith("QUERY_")) {// 如果有需要过滤的字段
				String[] filterParams = StringUtils.split(name, "_");
				if (filterParams.length == 4) {
					String columnName = filterParams[1].replaceAll("#", ".");// 要过滤的字段名称
					String columnType = filterParams[2];// 字段类型
					String operator = filterParams[3];// SQL操作符
					
					if (hql.toString().indexOf(" where 1=1") < 0) {
						hql.append("  where 1=1 ");
					}
					String param = name.replaceAll("\\.", "");
					hql.append(" and " + columnName + " "
							+ getSqlOperator(operator) + " :"+param+ " ");// 拼HQL
					params.put(param,
							getObjValue(columnType, operator, value));// 添加参数
				}
			}
		}
	}
	
	public void addEQFilter(String name, Object value) {
		if (name != null && value != null) {
			if (hql.toString().indexOf(" where 1=1") < 0) {
				hql.append("  where 1=1 ");
			}			
			if (name.indexOf(".") < 1) {
				name = "t." + name;
			}
			String param = name.replaceAll("\\.", "");
			hql.append(" and " + name + " "
					+ getSqlOperator("EQ") + " :"+ param + " ");// 拼HQL
			params.put(param,value);// 添加参数
		}
	}
	
	public void addLeftJoinEQFilter(String name,String joinSql, Object value) {
		if (name != null && value != null) {
			hql.insert(0, joinSql);			
			if (hql.toString().indexOf(" where 1=1") < 0) {
				hql.append("  where 1=1 ");
			}
			String param = name.replaceAll("\\.", "");
			hql.append(" and " + name + " "
					+ getSqlOperator("EQ") + " :"+param+ " ");// 拼HQL
			params.put(param,value);// 添加参数
		}
	}
	
	public void addLeftJoinLKFilter(String name,String joinSql, Object value) {
		if (name != null && value != null) {
			hql.insert(0, joinSql);			
			if (hql.toString().indexOf(" where 1=1") < 0) {
				hql.append("  where 1=1 ");
			}
			String param = name.replaceAll("\\.", "");
			hql.append(" and " + name + " "
					+ getSqlOperator("LK") + " :"+param+ " ");// 拼HQL
			params.put(param,"%%"+value+"%%");// 添加参数
		}
	}
	
	public void addLeftJoinINFilter(String name,String joinSql, Object value) {
		if (name != null && value != null) {
			hql.insert(0, joinSql);
			
			if (hql.toString().indexOf(" where 1=1") < 0) {
				hql.append("  where 1=1 ");
			}
			//String param = name.replaceAll("\\.", "");
			//hql.append(" and " + name + " IN(" + " :"+param +" ) ");// 拼HQL
			hql.append(" and " + name + " IN(" + value +") ");// 拼HQL
			//params.put(param,value);// 添加参数
		}
	}
	
	public void addSql(String joinSql) {
		hql.append(joinSql);// 拼HQL
	}
	
	public void addLKFilter(String name, Object value) {
		if (name != null && value != null) {
			if (hql.toString().indexOf(" where 1=1") < 0) {
				hql.append("  where 1=1 ");
			}
			if (name.indexOf(".") < 1) {
				name = "t." + name;
			}
			String param = name.replaceAll("\\.", "");
			hql.append(" and " + name + " "
					+ getSqlOperator("LK") + " :"+param + " ");// 拼HQL
			params.put(param,"%%"+value+"%%");// 添加参数
		}
	}

	/**
	 * 将String值转换成Object，用于拼写HQL，替换操作符和值
	 * 
	 * S:String L:Long I:Integer D:Date ST:Short BD:BigDecimal FT:Float
	 * 
	 * @param columnType
	 * @param operator
	 * @param value
	 * @return
	 */
	private Object getObjValue(String columnType, String operator, String value) {
		if (StringUtils.equalsIgnoreCase(columnType, "S")) {
			if (StringUtils.equalsIgnoreCase(operator, "LK")) {
				value = "%%" + value + "%%";
			} else if (StringUtils.equalsIgnoreCase(operator, "RLK")) {
				value = value + "%%";
			} else if (StringUtils.equalsIgnoreCase(operator, "LLK")) {
				value = "%%" + value;
			}
			return value;
		}
		if (StringUtils.equalsIgnoreCase(columnType, "L")) {
			return Long.parseLong(value);
		}
		if (StringUtils.equalsIgnoreCase(columnType, "I")) {
			return Integer.parseInt(value);
		}
		if (StringUtils.equalsIgnoreCase(columnType, "D")) {
			try {
				return DateUtils.parseDate(value, new String[] {
						"yyyy-MM-dd HH:mm:ss", "yyyy-MM-dd HH:mm",
						"yyyy-MM-dd", "yyyy/MM/dd" });
			} catch (ParseException e) {
				e.printStackTrace();
			}
		}
		if (StringUtils.equalsIgnoreCase(columnType, "ST")) {
			return Short.parseShort(value);
		}
		if (StringUtils.equalsIgnoreCase(columnType, "BD")) {
			return BigDecimal.valueOf(Long.parseLong(value));
		}
		if (StringUtils.equalsIgnoreCase(columnType, "FT")) {
			return Float.parseFloat(value);
		}
		return null;
	}

	public final String getTableName() {
		return tableName;
	}

	public final void setTableName(String tableName) {
		this.tableName = tableName;
	}	
	
}
